CREATE PROCEDURE RRHH_BUSCAR_EMPLEADO_L
@NOMBRE VARCHAR(100),
@APPATERNO VARCHAR(100),
@APMATERNO VARCHAR(100),
@SEXO VARCHAR(20),
@DOCIDENTIDAD VARCHAR(20),
@AREA VARCHAR(100),
@CARGO VARCHAR(100),
@ESTADOTRAN INTEGER OUTPUT
AS

--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

DECLARE @IDAR INTEGER
DECLARE @IDCA INTEGER
DECLARE @IDES INTEGER

BEGIN TRY
	--buscamos el id del area
	SET @IDAR = (SELECT IDAREA FROM AREA
				WHERE DESCRIPCION = @AREA)
	
	--buscamos el id del cargo
	SET @IDCA = (SELECT IDCARGO FROM CARGO
				WHERE DESCRIPCION = @CARGO AND IDAREA = @IDAR)
				
	--buscamos el id del estado "Despedido"
	SET @IDES = (SELECT IDESTADO FROM ESTADOEMPLEADO
				WHERE descripcion = 'Eliminado')
	
	IF (@IDAR IS NULL) AND (@IDCA IS NULL)
	BEGIN	
		SELECT * FROM EMPLEADO
		WHERE nombre LIKE @NOMBRE +'%' AND
			  apPaterno LIKE @APPATERNO + '%' AND
			  apMaterno LIKE @APMATERNO + '%' AND
			  sexo LIKE @SEXO + '%' AND
			  docIdentidad LIKE @DOCIDENTIDAD + '%' AND
			  idEstado <> @IDES
	END
	
	IF (@IDAR IS NULL) AND (@IDCA IS NOT NULL)
	BEGIN	
		SELECT * FROM EMPLEADO
		WHERE nombre LIKE @NOMBRE +'%' AND
			  apPaterno LIKE @APPATERNO + '%' AND
			  apMaterno LIKE @APMATERNO + '%' AND
			  sexo LIKE @SEXO + '%' AND
			  docIdentidad LIKE @DOCIDENTIDAD + '%' AND
			  idCargo = @IDCA AND idEstado <> @IDES
	END
	
	IF (@IDAR IS NOT NULL) AND (@IDCA IS NULL)
	BEGIN	
		SELECT * FROM EMPLEADO
		WHERE nombre LIKE @NOMBRE +'%' AND
			  apPaterno LIKE @APPATERNO + '%' AND
			  apMaterno LIKE @APMATERNO + '%' AND
			  sexo LIKE @SEXO + '%' AND
			  docIdentidad LIKE @DOCIDENTIDAD + '%' AND
			  @IDAR = idArea AND idEstado <> @IDES
	END
	
	IF (@IDAR IS NOT NULL) AND (@IDCA IS NOT NULL)
	BEGIN	
		SELECT * FROM EMPLEADO
		WHERE nombre LIKE @NOMBRE +'%' AND
			  apPaterno LIKE @APPATERNO + '%' AND
			  apMaterno LIKE @APMATERNO + '%' AND
			  sexo LIKE @SEXO + '%' AND
			  docIdentidad LIKE @DOCIDENTIDAD + '%' AND
			  @IDAR = idArea AND @IDCA = idCargo AND idEstado <> @IDES
	END
	
	SET @ESTADOTRAN = 1
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	SET @ESTADOTRAN = 0
	ROLLBACK TRANSACTION
END CATCH